﻿using System;
using System.Collections.Generic;
using System.Reflection;
using VerbrauchProcessor.DatabaseAccess.DataModel;
using Excel = Microsoft.Office.Interop.Excel;
using DataAnalyzer;

namespace VerbrauchProcessor.Export
{
	public class ExcelExport
	{
		// Create a logger for use in this class
		private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
		// NOTE that using System.Reflection.MethodBase.GetCurrentMethod().DeclaringType
		// is equivalent to typeof(LoggingExample) but is more portable
		// i.e. you can copy the code directly into another class without
		// needing to edit the code.


		/// <summary>
		/// 
		/// </summary>
		/// <param name="measurements"></param>
		public void ExportData(IList<MeasurementIntervall> measurementIntervalls)
		{

			if (measurementIntervalls.Count == 0)
			{
				throw new Exception("Measurement intervalls are empty");
			}


			try
			{
				//Start Excel and get Application object.
				Excel.Application excelApplication = excelApplication = new Excel.Application();
				excelApplication.Visible = true;

				//Get a new workbook.
				Excel._Workbook excelWorkbook = (Excel._Workbook)(excelApplication.Workbooks.Add(Missing.Value));

				// Create data sheet
				Excel._Worksheet dataSheet = (Excel._Worksheet)excelWorkbook.ActiveSheet;
				dataSheet.Name = "Daten";

				//Add table headers going cell by cell.
				dataSheet.Cells[1, 1] = "Start Datum";
				dataSheet.Cells[1, 2] = "End Datum";
				dataSheet.Cells[1, 3] = "Gas/Tag";
				dataSheet.Cells[1, 4] = "Strom/Tag";
				dataSheet.Cells[1, 5] = "Wasser/Tag";

				//Format A1:D1 as bold, vertical alignment = center.
				Excel.Range excelRange = dataSheet.get_Range("A1", "E1");
				excelRange.Font.Bold = true;
				excelRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

				// Create an array to multiple values at once.
				string[,] dates = new String[measurementIntervalls.Count, 2];
				double[,] measurements = new double[measurementIntervalls.Count, 3];

				int i = 0;
				foreach (MeasurementIntervall mi in measurementIntervalls)
				{
					dates[i, 0] = mi.StartDate.Date.ToShortDateString();
					dates[i, 1] = mi.EndDate.Date.ToShortDateString();

					measurements[i, 0] = Convert.ToDouble(mi.GasPerDay_m3);
					measurements[i, 1] = Convert.ToDouble(mi.ElectricityPerDay_kwh);
					measurements[i, 2] = Convert.ToDouble(mi.WaterPerDay_m3);
					i++;
				}
				//Fill A2:BX with an array of values (the dates).
				excelRange = dataSheet.get_Range("A2", "B" + (measurementIntervalls.Count+1).ToString());
				excelRange.Value2 = dates;

				//Fill A2:BX with an array of values (the measurements).
				excelRange = dataSheet.get_Range("C2", "E" + (measurementIntervalls.Count+1).ToString());
				excelRange.Value2 = measurements;
				excelRange.NumberFormat = "0.000";

				/**Create Averages**/
				CreateAverageTable(measurementIntervalls, dataSheet);

				/***Create charts***/
				excelRange = dataSheet.get_Range("C2", "C" + (measurementIntervalls.Count + 1).ToString());
				CreateExcelCharts(dataSheet, measurementIntervalls, excelRange, "Gas", "m3 /Tag");


				excelRange = dataSheet.get_Range("D2", "D" + (measurementIntervalls.Count+1).ToString());
				CreateExcelCharts(dataSheet, measurementIntervalls, excelRange, "Strom","KWH /Tag");


				excelRange = dataSheet.get_Range("E2", "E" + (measurementIntervalls.Count + 1).ToString());
				CreateExcelCharts(dataSheet, measurementIntervalls, excelRange, "Wasser","m3 /Tag");
				
				/***/

				//Make sure Excel is visible and give the user control
				//of Microsoft Excel's lifetime.
				excelApplication.Visible = true;
				excelApplication.UserControl = true;
			}
			catch (Exception theException)
			{
				String errorMessage;
				errorMessage = "Error: ";
				errorMessage = String.Concat(errorMessage, theException.Message);
				errorMessage = String.Concat(errorMessage, " Line: ");
				errorMessage = String.Concat(errorMessage, theException.Source);

				log.Error("Error", theException);
			}
		}

		/// <summary>
		/// Computes and prints the data averages
		/// </summary>
		private void CreateAverageTable(IList<MeasurementIntervall> mis, Excel._Worksheet dataSheet)
		{
			AverageValues avgValues= new AverageValues(mis);
			dataSheet.Cells[1, 7] = "Durchschnitt Wasser/Tag";
			dataSheet.Cells[2, 7] = avgValues.WaterAverage;

			dataSheet.Cells[1, 8] = "Durchschnitt Gas/Tag";
			dataSheet.Cells[2, 8] = avgValues.GasAverage;
			
			dataSheet.Cells[1, 9] = "Durchschnitt Strom/Tag";
			dataSheet.Cells[2, 9] = avgValues.ElectricityAverage;
		}

		/// <summary>
		/// Draws the time diagrams.
		/// </summary>
		private void CreateExcelCharts( Excel._Worksheet excelWorkSheet,
										IList<MeasurementIntervall> measurementIntervalls, 
										Excel.Range excelRange, 
										string sDiagrammTitle,
										string sValueTitle)
		{
			Excel._Workbook excelWorkBook = excelWorkSheet.Parent;
			Excel.Series oSeries;
			Excel._Chart oChart;

			//Add a Chart for the selected data.
			excelWorkBook = (Excel._Workbook)excelWorkSheet.Parent;
			oChart = (Excel._Chart)excelWorkBook.Charts.Add(Missing.Value,
															Missing.Value,
															Missing.Value,
															Missing.Value);
			oChart.Name = sDiagrammTitle;
			
			// chart wizard
			oChart.ChartWizard( excelRange,
								Excel.XlChartType.xlLine,
								Missing.Value,
								Excel.XlRowCol.xlColumns,
								Missing.Value,
								Missing.Value,
								false,
								sDiagrammTitle,
								Missing.Value,
								sValueTitle,
								Missing.Value);
			
			// data setup y axis data
			oChart.SetSourceData(excelRange, Missing.Value);

			// data setup x axis data
			oSeries = (Excel.Series)oChart.SeriesCollection(1);
			oSeries.XValues = excelWorkSheet.get_Range("A2", "A" + (measurementIntervalls.Count + 1).ToString());

			// format y value axis
			Excel.Axis valAxis = oChart.Axes(Excel.XlAxisType.xlValue);
			valAxis.HasMajorGridlines = true;
			valAxis.HasDisplayUnitLabel = false;
			
			// format x value axis
			Excel.Axis catAxis = oChart.Axes(Excel.XlAxisType.xlCategory);
			catAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
		}
	}

}
